﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;


public partial class Suppliers : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.IsPostBack == false)
        {
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            loadSupplier();
        }

    }
    #region Code
    void loadSupplier()
    {

        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Production.Suppliers", con);
            DataTable dt = new DataTable("Suppliers");
            da.Fill(dt);
            gvSuppliers.AutoGenerateSelectButton = true;
            gvSuppliers.AllowPaging = true;
            gvSuppliers.PageSize = 7;
            gvSuppliers.DataSource = dt;
            gvSuppliers.DataBind();
            
        }
        catch (Exception)
        {
            
            Response.Write("<script>alert('" + "Load function is failed" + "')</script>");
        }

    }

    void addSupplier()
    {

        try
        {
            
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Suppliers_Insert";

            SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
            param.Value = txtCompanyName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
            param.Value = txtContactTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
            param.Value = txtContactName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = txtCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = ddlCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = txtPhone.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            if (txtRegion.Text.Trim() == "")
            {
                param.Value = DBNull.Value;
            }
            else param.Value = txtRegion.Text;

            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            if (txtPostalCode.Text.Trim() == "")
            {
                param.Value = DBNull.Value;
            }
            else param.Value = txtPostalCode.Text;

            cmd.Parameters.Add(param);

            param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
            if (txtFax.Text.Trim() == "")
            {
                param.Value = DBNull.Value;
            }
            else param.Value = txtFax.Text;

            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();

            con.Close();
            loadSupplier();
            Response.Write("<script>alert('" + "Add success!" + "')</script>");
        }
        catch (Exception)
        {
            
            Response.Write("<script>alert('" + "Add function is failed!" + "')</script>");
        }

    }

    void newSuppliers()
    {
        try
        {
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtSupplierId.Text = "";
            txtCompanyName.Text = "";
            txtContactName.Text = "";
            txtContactTitle.Text = "";
            txtAddress.Text = "";
            txtCity.Text = "";
            ddlCountry.SelectedIndex = 0;
            txtRegion.Text = "";
            txtPostalCode.Text = "";
            txtPhone.Text = "";
            txtFax.Text = "";
            txtSearch.Text = "";
            ddlSearchBy.SelectedIndex = 0;

        }
        catch (Exception)
        {
            
           Response.Write("<script>alert('" + "Create new supplier is failed!" + "')</script>");
        }
    }

    void selectSupplier()
    {
        try
        {
            if (gvSuppliers.Rows.Count > 0)
            {
                btnDelete.Enabled = true;
                btnUpdate.Enabled = true;
                btnAdd.Enabled = false;
                GridViewRow r = gvSuppliers.SelectedRow;
                txtSupplierId.Text = Server.HtmlDecode(r.Cells[1].Text.ToString());
                txtCompanyName.Text = Server.HtmlDecode(r.Cells[2].Text.ToString());
                txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text.ToString());
                txtContactTitle.Text = Server.HtmlDecode(r.Cells[4].Text.ToString());
                txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text.ToString());
                txtCity.Text = Server.HtmlDecode(r.Cells[6].Text.ToString());
                txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text.ToString());
                txtPostalCode.Text = Server.HtmlDecode(r.Cells[8].Text.ToString());
                ddlCountry.Text = Server.HtmlDecode(r.Cells[9].Text.ToString());
                txtPhone.Text = Server.HtmlDecode(r.Cells[10].Text.ToString());
                txtFax.Text = Server.HtmlDecode(r.Cells[11].Text.ToString());
            }
        }
        catch (Exception)
        {
            
            Response.Write("<script>alert('" + "Select function is failed!" + "')</script>");
        }
    }

    void updateSuppliers()
    {
        try
        {
            txtSupplierId.Enabled = true;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.sp_Suppliers_Update";

            SqlParameter param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = txtSupplierId.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
            param.Value = txtCompanyName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
            param.Value = txtContactName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
            param.Value = txtContactTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = txtCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            param.Value = txtRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            param.Value = txtPostalCode.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = ddlCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = txtPhone.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
            param.Value = txtFax.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            con.Close();
            loadSupplier();
            Response.Write("<script>alert('" + "Update success!" + "')</script>");
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Update function is failed!" + "')</script>");
        }
    }

    void deleteSuppliers()
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.sp_Suppliers_Delete";

            SqlParameter param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = txtSupplierId.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            con.Close();
            newSuppliers();
            loadSupplier();
            Response.Write("<script>alert('" + "Delete success!" + "')</script>");
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Delete dunction is failed!" + "')</script>");
        }
    }

    void searchSuppliers()
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            if (ddlSearchBy.SelectedIndex == 0)
            {
                cmd.CommandText = string.Format("SELECT * FROM Production.Suppliers WHERE companyname LIKE '%{0}%'", txtSearch.Text);

            }
            else
            {
                if (ddlSearchBy.SelectedIndex == 1)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Production.Suppliers WHERE contactname LIKE '%{0}%'", txtSearch.Text);
                }

            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("Production.Suppliers");
            da.Fill(dt);

            gvSuppliers.DataSource = dt;
            gvSuppliers.DataBind();
            gvSuppliers.AutoGenerateSelectButton = true;
            gvSuppliers.AllowPaging = true;
            gvSuppliers.PageSize = 7;
            Session["issearch"] = true;
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Search function is failed" + "')</script>");
        }
    }

    #endregion








    #region Event
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        addSupplier();
    }
    protected void gvSupplier_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        if (!(bool)Session["issearch"])
            try
            {
                gvSuppliers.PageIndex = e.NewPageIndex;
                loadSupplier();
                Session["issearch"] = false;
            }
            catch (Exception ex)
            {
                //Response.Write("<script>alert('" + "Failed Selected Index Changed" + "')</script>");
            }
        else
        {
            try
            {
                gvSuppliers.PageIndex = e.NewPageIndex;
                btnSearch_Click(sender,e);
            }
            catch (Exception ex)
            {
                //Response.Write("<script>alert('" + "Failed Selected Index Changed" + "')</script>");
            }
        }



    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        newSuppliers();
    }
    protected void gvSuppliers_SelectedIndexChanged(object sender, EventArgs e)
    {
        selectSupplier();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        updateSuppliers();
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteSuppliers();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        searchSuppliers();
        
    }
    #endregion
    protected void btnReload_Click(object sender, EventArgs e)
    {
        newSuppliers();
        loadSupplier();
    }
}
